let mysqlConnection = require("./mysqlConfig")
let Utils = require("./Utils")


module.exports = {
    selectAllTasks: function () {
        return new Promise((resolve, reject) => {
            let selectAllTasksSQL = "select * from all_tasks";
            mysqlConnection.query(selectAllTasksSQL, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },
    selectAllTasksEscTime: function () {
        return new Promise((resolve, reject) => {
            let selectAllTasksSQL = "select * from all_tasks order by due_date";
            mysqlConnection.query(selectAllTasksSQL, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },
    selectAllTasksDescTime: function () {
        return new Promise((resolve, reject) => {
            let selectAllTasksSQL = "select * from all_tasks order by due_date desc";
            mysqlConnection.query(selectAllTasksSQL, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },
    deleteATask: function (id) {
        return new Promise((resolve, reject) => {
            let deleteATaskSQL = `DELETE FROM tasks.all_tasks WHERE id = ${id}`;
            mysqlConnection.query(deleteATaskSQL, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },
    updateTask: function (TasksModel) {
        return new Promise((resolve, reject) => {
            let updateTaskSQL = `UPDATE tasks.all_tasks t SET t.title = '${TasksModel.title}', t.catagory = '${TasksModel.catagory}', t.description = '${TasksModel.description}', t.due_date = ${TasksModel.due_date}, t.marks = ${TasksModel.marks} WHERE t.id = ${TasksModel.id}`;
            console.log(updateTaskSQL)
            mysqlConnection.query(updateTaskSQL, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },
    insertTask: function (TasksModel) {
        return new Promise((resolve, reject) => {
            let insertTaskSQL = `INSERT INTO tasks.all_tasks (id, title, catagory, description, due_date, marks) VALUES (${TasksModel.id}, '${TasksModel.title}', '${TasksModel.catagory}', '${TasksModel.description}', ${TasksModel.due_date}, ${TasksModel.marks})`
            console.log(insertTaskSQL)
            mysqlConnection.query(insertTaskSQL, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },
    selectTodayTasks: function () {
        return new Promise((resolve, reject) => {

            let selectTodayTasksSQL = `
         select a.id, a.title, a.catagory, a.description, a.due_date,a.marks ,u.id as userid,concat(u.fammily_name,u.given_name) as name
from user u
join user_tasks ut
    on u.id = ut.user_id
join all_tasks a
    on ut.tasks_id = a.id
    
    where due_date >= ${Utils.TodayZeroTime()} && due_date < ${Utils.TommorrowZeroTime()}`
            // let selectTodayTasksSQl2=`select * from all_tasks where due_date >= ${Utils.TodayZeroTime()} && due_date < ${Utils.TommorrowZeroTime()}`
            console.log(selectTodayTasksSQL)
            mysqlConnection.query(selectTodayTasksSQL, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },


    selectTommorrowTasks: function () {
        return new Promise((resolve, reject) => {

            let selectTomorrowTasksSQl = ` select a.id, a.title, a.catagory, a.description, a.due_date,a.marks, u.id as userid,concat(u.fammily_name,u.given_name) as name
from user u
join user_tasks ut
    on u.id = ut.user_id
join all_tasks a
    on ut.tasks_id = a.id
    where due_date >= ${Utils.TommorrowZeroTime()} && due_date < ${Utils.TheDayAfterTommorrowZeroTime()}`
            mysqlConnection.query(selectTomorrowTasksSQl, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },
  selectTodayTasksEscTime: function () {
        return new Promise((resolve, reject) => {

            let selectTodayTasksSQL = `
         select a.id, a.title, a.catagory, a.description, a.due_date,a.marks ,u.id as userid,concat(u.fammily_name,u.given_name) as name
from user u
join user_tasks ut
    on u.id = ut.user_id
join all_tasks a
    on ut.tasks_id = a.id
   
    where due_date >= ${Utils.TodayZeroTime()} && due_date < ${Utils.TommorrowZeroTime()}
     order by a.due_date
     `
            // let selectTodayTasksSQl2=`select * from all_tasks where due_date >= ${Utils.TodayZeroTime()} && due_date < ${Utils.TommorrowZeroTime()}`
            console.log(selectTodayTasksSQL)
            mysqlConnection.query(selectTodayTasksSQL, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },
    selectTodayTasksDescTime: function () {
        return new Promise((resolve, reject) => {

            let selectTodayTasksSQL = `
         select a.id, a.title, a.catagory, a.description, a.due_date,a.marks ,u.id as userid,concat(u.fammily_name,u.given_name) as name
from user u
join user_tasks ut
    on u.id = ut.user_id
join all_tasks a
    on ut.tasks_id = a.id

    where due_date >= ${Utils.TodayZeroTime()} && due_date < ${Utils.TommorrowZeroTime()}
        order by a.due_date desc
    `
            // let selectTodayTasksSQl2=`select * from all_tasks where due_date >= ${Utils.TodayZeroTime()} && due_date < ${Utils.TommorrowZeroTime()}`
            console.log(selectTodayTasksSQL)
            mysqlConnection.query(selectTodayTasksSQL, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },


    selectTommorrowTasksEscTime: function () {
        return new Promise((resolve, reject) => {

            let selectTomorrowTasksSQl = ` select a.id, a.title, a.catagory, a.description, a.due_date,a.marks, u.id as userid,concat(u.fammily_name,u.given_name) as name
from user u
join user_tasks ut
    on u.id = ut.user_id
join all_tasks a
    on ut.tasks_id = a.id
  
    where due_date >= ${Utils.TommorrowZeroTime()} && due_date < ${Utils.TheDayAfterTommorrowZeroTime()}
      order by a.due_date
      `
            mysqlConnection.query(selectTomorrowTasksSQl, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    },
  selectTommorrowTaskDescTime: function () {
        return new Promise((resolve, reject) => {

            let selectTomorrowTasksSQl = ` select a.id, a.title, a.catagory, a.description, a.due_date,a.marks, u.id as userid,concat(u.fammily_name,u.given_name) as name
from user u
join user_tasks ut
    on u.id = ut.user_id
join all_tasks a
    on ut.tasks_id = a.id
 
    where due_date >= ${Utils.TommorrowZeroTime()} && due_date < ${Utils.TheDayAfterTommorrowZeroTime()}
       order by a.due_date desc
    `
            mysqlConnection.query(selectTomorrowTasksSQl, function (error, results) {
                if (error) {
                    reject(error)
                } else {
                    resolve(results);
                }
            })
        })
    }

}